Easy2Siksha

GNDU Question Paper-2023

Ba/BSc 5

Semester

COMPUTER SCIENCE

(Database Management System & Oracle)

Time Allowed: 3 Hrs. Maximum Marks: 75

Note: Attempt Five questions in all, selecting at least One question from each section. The

Fifth question may be attempted from any section.

SECTION-A

1. Describe the term DBMS and its advantages. Draw and explain the detailed system

architecture of DBMS.

2. Discuss the main characteristics of the database approach and specify how it differs

from traditional file system. Explain the importance of avoiding NULL values in a database.

SECTION-B

3. Explain in detail the Relational, Hierarchical and network models for an example data

base design.

4. What is the need of normalization? Explain BCNF, INF, 2NF and 4NF normal forms.

SECTION-C

5. Describe the concept of Referential Integrity. List and explain the common data types

available in SQL.

Easy2Siksha

6. By considering an example describe various data control and update operations in SQL.

SECTION-D

7. Why the concurrency control is required in data bases? Explain various concurrency

control mechanisms.

8. Write short notes on the following:-

(a) Big Data Analytics

(b) NoSQL

Easy2Siksha

GNDU Answer Paper-2023

Ba/BSc 5

Semester

COMPUTER SCIENCE

(Database Management System & Oracle)

Time Allowed: 3 Hrs. Maximum Marks: 75

Note: Attempt Five questions in all, selecting at least One question from each section. The

Fifth question may be attempted from any section.

SECTION-A

1. Describe the term DBMS and its advantages. Draw and explain the detailed system

architecture of DBMS.

Ans: Database Management System (DBMS)

A Database Management System (DBMS) is software that helps in managing, organizing, and

retrieving data from a database in an efficient and secure way. Think of it as a system that

allows multiple users to interact with a large collection of data without the need for the

users to know exactly how the data is stored. In simple words, DBMS acts as an interface

between the users and the database to handle, update, and retrieve the required

information as per user needs.

Key Components of DBMS:

1. Database: A structured collection of data (like a large file of information).

2. DBMS Software: The program or system that manages the database.

3. User/Administrator: Individuals or programs that use the DBMS to interact with the

data.

Functions of DBMS:

• Data Storage, Retrieval, and Update: Helps in storing data and makes it easy to

retrieve or modify it.

• User Interaction: Allows users to communicate with the database without needing

to understand how the data is stored or organized.

Easy2Siksha

• Data Integrity and Security: Ensures that data is accurate and prevents unauthorized

access to data.

• Backup and Recovery: Helps in recovering data if there's any system failure or other

issues.

Advantages of DBMS

1. Data Redundancy Control: In the absence of a DBMS, multiple copies of the same

data are stored in different places. This leads to redundancy and wastage of space.

DBMS ensures that data redundancy is controlled by keeping a single copy of data

available for multiple users.

2. Data Integrity: DBMS makes sure that the data is accurate and consistent by

following certain rules (integrity constraints). For example, you can't have two users

with the same unique ID.

3. Data Security: It provides several ways to secure sensitive data from unauthorized

users. For example, only certain users may have the rights to modify or view specific

data, ensuring privacy.

4. Data Abstraction: With DBMS, the users don't need to know where the data is

physically stored or how it's managed. DBMS provides a simplified view of the data

to the users. It hides the complexities of the underlying system.

5. Multiple User Access: DBMS allows multiple users to access the database

simultaneously without affecting each other's work. For example, in a banking

system, multiple users can access the same data at the same time, such as checking

account details or processing a transaction.

6. Backup and Recovery: It provides automated backup features and recovery options,

which means in case of system failure or other issues, you can restore the data

easily.

7. Reduced Application Development Time: DBMS provides built-in features such as

query languages (SQL), which allow for faster development of applications as

developers can easily communicate with the database without needing to write

complex code for basic operations.

8. Increased Data Sharing: DBMS promotes sharing of data by providing a centralized

system where multiple departments or individuals can access the required data

without any redundancy.

9. Better Decision Making: With DBMS, large amounts of data can be organized, and

meaningful insights can be derived, aiding decision-making processes for businesses

and organizations.

Easy2Siksha

System Architecture of DBMS

The architecture of a DBMS refers to the design and layout of the database system. It helps

define how data will be stored, processed, and accessed by users. The architecture can be

divided into two main types:

1. Two-Tier Architecture

2. Three-Tier Architecture

However, to explain the overall system in a more comprehensive way, we’ll focus on the

three-tier architecture since it’s more commonly used in modern databases.

Three-Tier Architecture of DBMS:

This architecture breaks the entire database system into three layers:

• 1. Presentation Layer (User Interface Layer)

• 2. Application Layer (Business Logic Layer)

• 3. Data Layer (Database Layer)

Let’s go through these layers one by one:

1. Presentation Layer (User Interface Layer):

o This is the topmost layer where users interact with the database system. It

includes various user interfaces such as applications, websites, or software

tools.

o Users can enter queries or commands through this interface. These could be

simple searches, like checking a product in an online store, or more complex

operations like running reports on data.

o It presents the data in a user-friendly format.

2. Application Layer (Business Logic Layer):

o This is the middle layer, often referred to as the logic layer. It’s where the

rules or business logic of the system are implemented.

o When a user makes a request (such as searching for a product or checking a

bank balance), this layer processes the request. It communicates with the

database, applies the necessary rules (like filtering, sorting, or calculations),

and sends the processed data back to the user.

o For example, in a banking system, if a user asks for their account balance, the

application layer will take the user's request, fetch the relevant data from the

database, and display it in the user interface.

Easy2Siksha

3. Data Layer (Database Layer):

o This is the lowest layer, and it consists of the database itself. It stores all the

data that the DBMS manages.

o It includes tables, records, and the actual storage systems that hold the data.

The DBMS interacts with this layer to retrieve, insert, update, or delete data.

o This layer also manages the data's storage on physical storage devices such as

hard drives.

Working of DBMS Architecture:

Let’s consider a real-life example to understand the working of the DBMS architecture.

Suppose a customer wants to check their order status from an online shopping website.

1. User Request:

o The customer opens the shopping app on their phone (this is the

Presentation Layer) and types in their order number to check the order

status.

2. Processing in Application Layer:

o The app takes this request and passes it to the Application Layer, where the

business logic is applied. The system needs to figure out what the user is

asking for and generate a query that will search the database for the required

order details.

3. Database Interaction:

o The Application Layer interacts with the Data Layer, sends the query to the

DBMS, and requests the required data from the database.

o The DBMS then checks its database, retrieves the necessary information, and

sends it back to the Application Layer.

4. Data Presentation:

o The Application Layer processes the retrieved data (like formatting it in a

readable way), and the Presentation Layer then displays the order status on

the customer’s screen.

This is a simplified example of how the three-tier architecture of DBMS works.

Types of DBMS:

1. Hierarchical DBMS:

o Data is organized in a tree-like structure where a single parent record can

have multiple child records. It's suitable for applications with hierarchical

data structures, like organizational charts.

Easy2Siksha

2. Network DBMS:

o This type allows more complex relationships between data. In this case, child

records can have multiple parents, forming a graph structure. It is more

flexible than the hierarchical model but still somewhat rigid compared to

modern DBMS.

3. Relational DBMS (RDBMS):

o This is the most widely used type of DBMS. Data is stored in tables (or

relations) where each record (row) in the table has a unique identifier (like a

primary key). It's flexible and allows complex queries. Examples include

MySQL, Oracle, and SQL Server.

4. Object-Oriented DBMS:

o Here, data is represented in the form of objects, much like how objects are

represented in object-oriented programming languages like Java. This is

useful in cases where data needs to be stored along with its

methods/functions.

Conclusion:

DBMS is a vital part of modern information systems, providing efficient data management,

security, and accessibility. By implementing data integrity and reducing redundancy, DBMS

has become an essential tool for businesses, governments, and institutions that handle large

amounts of data. It simplifies complex operations and provides a user-friendly environment,

ensuring that even non-technical users can work with data seamlessly. The three-tier

architecture of DBMS, along with its various types, shows how advanced and adaptable

these systems have become in today's digital world.

2. Discuss the main characteristics of the database approach and specify how it differs

from traditional file system. Explain the importance of avoiding NULL values in a database.

Ans: Database Approach: Main Characteristics

A database is a structured way to store, manage, and retrieve data. It differs from the

traditional file system in several ways. Let's break down the key characteristics of the

database approach and explain how it's different.

1. Data Independence:

o In the database approach, the data is separated from the programs that use

it. This means changes can be made to the database without needing to

modify the applications that depend on it. For example, if you add a new field

Easy2Siksha

in a database, your application might still work without changing the code,

whereas in a traditional file system, changes to the file structure might

require code changes.

2. Reduced Data Redundancy:

o Redundancy means storing the same piece of data in multiple places. In

traditional file systems, data might be repeated across different files, leading

to redundancy. The database approach reduces redundancy by using a single,

central database that all applications can access.

3. Data Integrity:

o Data integrity refers to maintaining accuracy and consistency in data over its

lifecycle. In a database, rules can be set to ensure that the data is correct and

reliable. For example, a rule might prevent entering an age of "200" for a

person. In traditional file systems, such integrity checks need to be manually

handled by the applications.

4. Data Security:

o Databases have built-in mechanisms to control who can see or modify the

data. Different users can have different access levels, which ensures that

sensitive data is protected. In contrast, in traditional file systems, it’s harder

to enforce security rules consistently across all files.

5. Concurrent Access:

o Multiple users or applications can access and manipulate the data at the

same time in a database without causing inconsistencies. Databases use

transaction control and locking mechanisms to ensure this. In traditional file

systems, it’s difficult to manage multiple accesses at once without data being

overwritten or lost.

6. Data Consistency:

o Databases ensure that the data remains consistent across all users and

applications. For example, if two users update the same data at the same

time, the database will ensure that one user’s update doesn’t override the

other’s.

7. Structured Query Language (SQL):

o Databases use SQL, a special language, to manage and retrieve data. SQL is

powerful and flexible, making it easier to interact with databases. Traditional

file systems don’t have such a language and rely on more complex

programming logic to manage files.

Easy2Siksha

8. Backup and Recovery:

o Databases have built-in tools to automatically back up data and recover from

failures. In case of a system crash, a database can be restored to its previous

state. Traditional file systems require manual intervention to back up files

and recover lost data.

9. Data Sharing:

o Databases are designed to allow data sharing among multiple users and

applications. A central repository is maintained, and authorized users can

access the data based on their permissions. This is difficult in traditional file

systems, where each file is often specific to a single application.

Difference Between Database and Traditional File System

Let’s compare the database approach with the traditional file system more clearly.

Feature

Database Approach

Traditional File System

Data

Independence

Data is independent of the

programs that use it.

Data and programs are tightly

integrated.

Data Redundancy

Redundancy is minimized.

High redundancy due to multiple

file copies.

Data Integrity

Ensured through rules and

constraints.

Needs to be handled by individual

applications.

Security

Centralized control over data

access.

Less control over access, dependent

on file-level permissions.

Concurrent

Access

Multiple users can access and

update data simultaneously.

Hard to manage concurrent access;

risks overwriting data.

Data Consistency

Data consistency is maintained

automatically.

Consistency must be enforced

manually by applications.

Backup and

Recovery

Automatic backups and recovery

options available.

Manual backups and recovery are

needed.

Data Sharing

Easily shared among multiple

applications and users.

Difficult to share between

applications.

Easy2Siksha

Why Avoid NULL Values in a Database

In databases, a NULL value represents the absence of a value or unknown data. While NULL

values have their use, it’s often important to avoid them for several reasons:

1. Data Integrity:

o NULL values can introduce ambiguity in data. For example, if a field for a

person’s phone number is NULL, it’s unclear whether the person has no

phone or the phone number is just unknown. Avoiding NULL values helps

maintain clear, consistent data.

2. Complexity in Queries:

o NULL values make database queries more complicated. For example,

comparing a NULL value to any other value often doesn’t return a simple

TRUE or FALSE but instead returns UNKNOWN. This requires special handling

in SQL queries, making them more complex and harder to maintain.

3. Mathematical Operations:

o NULL values can cause issues when performing mathematical operations. For

example, if a column contains a NULL value and you try to calculate the sum,

the result might not be what you expect. Avoiding NULLs simplifies such

calculations.

4. Joining Tables:

o When you join tables in a database, NULL values can prevent you from

properly matching rows. For example, if you try to join two tables using a

column that contains NULLs, those rows with NULL won’t appear in the result

set, leading to incomplete data.

5. Data Consistency:

o NULL values can lead to inconsistent data. If some fields are NULL and others

aren’t, it becomes harder to maintain consistency across records. For

example, in a table of employees, if some records have NULL values for the

salary column, it’s unclear how to treat those records compared to those

with a salary value.

6. Storage Efficiency:

o While NULL values don’t take up much space, their presence can lead to

inefficient storage and slower performance. When possible, using default

values or markers like "N/A" instead of NULL can improve efficiency.

7. Referential Integrity:

o In relational databases, referential integrity ensures that relationships

between tables are consistent. NULL values can break this consistency if they

Easy2Siksha

appear in foreign key columns, leading to confusion about how records in

different tables are related.

How to Avoid NULL Values

Here are some strategies for avoiding NULL values in a database:

1. Use Default Values:

o Instead of allowing a field to be NULL, you can set a default value. For

example, if a phone number is missing, you can set the field to a default value

like "N/A" or "Unknown" instead of NULL.

2. Use NOT NULL Constraints:

o Most databases allow you to define constraints on columns, specifying that

they cannot be NULL. This forces users to enter a value when adding or

updating data.

3. Input Validation:

o Ensure that your application performs proper validation of data before it’s

entered into the database. For example, if a user leaves a required field

empty, the system should prompt them to enter a value instead of allowing a

NULL.

4. Handle Missing Data in the Application:

o If some data is truly missing or unknown, handle it at the application level

rather than letting it enter the database as a NULL. For example, you could

use placeholders or provide a "no data" option.

Conclusion

The database approach offers several advantages over traditional file systems, including

better data management, reduced redundancy, improved integrity, and easier data sharing.

Avoiding NULL values in a database is also important for maintaining data consistency,

simplifying queries, and ensuring smooth operations. By understanding the differences

between the database approach and traditional file systems and recognizing the importance

of NULL handling, one can build more efficient, reliable, and user-friendly data systems.

Easy2Siksha

SECTION-B

3. Explain in detail the Relational, Hierarchical and network models for an example data

base design.

Ans: To understand the three main types of database models—Relational, Hierarchical, and

Network—let’s break each down with simple explanations and examples. These models

represent different ways of organizing data in a database.

1. Relational Database Model

Overview: The relational model is the most widely used database model. It organizes data

into tables (also called relations), where each table contains rows and columns. Each row

represents a single record, and each column represents a specific attribute of that record.

Think of a table like a spreadsheet. Every row (record) contains data for a specific entity, and

every column (field) represents attributes of that entity.

Key Concepts:

• Table (Relation): A collection of data organized in rows and columns.

• Row (Tuple): A single record in a table, such as a person’s name, age, and address.

• Column (Attribute): Characteristics or properties of the data, like Name, Age, or

Address.

• Primary Key: A unique identifier for each record (like a person’s ID number).

• Foreign Key: A field that links two tables together.

Example:

Let’s say we have two tables: Students and Courses.

• Students Table:

Student_ID

Name

Age

Major

John

Maria

Physics

Aisha

Math

Easy2Siksha

• Courses Table:

Course_ID

Course_Name

Student_ID

101

DBMS

102

Physics

103

Algebra

In the Students table, the Student_ID is the primary key. In the Courses table, the

Student_ID is a foreign key, which links to the Students table.

Advantages:

• Flexibility: You can easily add or delete tables and update data without affecting the

entire system.

• Data Integrity: Relational databases support constraints, which help maintain

accuracy and consistency of data.

• Scalability: Relational databases can handle large amounts of data.

Disadvantages:

• Complexity: As data grows and relationships between tables become more

complicated, managing and querying relational databases can be tricky.

• Performance: For very large databases, relational models can become slow if not

properly optimized.

2. Hierarchical Database Model

Overview: The hierarchical model organizes data in a tree-like structure, where each record

has a single parent, and records are linked in a parent-child relationship. This model is one

of the earliest used in database design.

In this model, each child can have only one parent, but a parent can have multiple children.

It is similar to a family tree, where each person (child) is linked to a single parent.

Key Concepts:

• Parent: The top-level record.

• Child: A subordinate record linked to a parent.

Easy2Siksha

• Root: The top-most node or record in the hierarchy.

• Tree Structure: Data is organized in a tree-like structure.

Example:

Consider a company with employees and departments. The hierarchy would look something

like this:

• Root (Company)

o Parent (Department)

▪ Child (Employee)

The Company is at the top of the hierarchy, and under the company are several

Departments. Each Department contains several Employees.

• Company:

o Department (HR)

▪ Employee (John)

▪ Employee (Sara)

o Department (IT)

▪ Employee (Mike)

▪ Employee (Alex)

In this structure, the Department is the parent, and each Employee is the child. Each

employee belongs to only one department, so there is a strict parent-child relationship.

Advantages:

• Efficiency: Because of its tree structure, data retrieval is fast, especially for large

datasets.

• Data Integrity: The model ensures strict parent-child relationships, so data is highly

structured.

Disadvantages:

• Rigid Structure: Hierarchical databases are inflexible because each child can only

have one parent. If you need to change the structure or add more relationships, it

can be difficult.

• Redundancy: Data can be duplicated, as each record can have only one parent.

• Complex Queries: If you need to query data that doesn't follow the parent-child

relationship, it can become complicated.

Easy2Siksha

3. Network Database Model

Overview: The network model is an extension of the hierarchical model but with more

flexibility. In the network model, a child can have more than one parent, allowing for more

complex relationships between data.

It’s a bit like a web, where multiple links connect different records, allowing for many-to-

many relationships.

Key Concepts:

• Set: A collection of records that are related to each other.

• Owner: The record that acts as the parent in a relationship.

• Member: The record that acts as the child in a relationship.

• Graph Structure: Data is organized in a graph, with multiple relationships between

records.

Example:

Imagine a university where professors can teach multiple courses, and courses can be

taught by multiple professors.

• Professor:

o Professor_ID

o Professor_Name

• Course:

o Course_ID

o Course_Name

• Teaches:

o Professor_ID

o Course_ID

Here, the Professor can be the parent in one relationship, and the Course can be the parent

in another relationship. The Teaches table links professors and courses, allowing many-to-

many relationships. A professor can teach many courses, and a course can be taught by

multiple professors.

Easy2Siksha

• Professor Table:

Professor_ID

Name

Dr. Lee

Dr. Patel

• Course Table:

Course_ID

Course_Name

101

DBMS

102

Networking

• Teaches Table:

Professor_ID

Course_ID

101

102

Here, Dr. Lee teaches both DBMS and Networking, and DBMS is taught by both Dr. Lee and

Dr. Patel. The network model allows this flexibility.

Advantages:

• Flexibility: Unlike the hierarchical model, the network model allows many-to-many

relationships, making it more adaptable.

• Efficiency: Data can be accessed quickly through multiple paths, reducing

redundancy.

Easy2Siksha

• Reduced Data Redundancy: The model can eliminate some of the redundancy found

in hierarchical models by allowing more complex relationships.

Disadvantages:

• Complexity: The network model can become complex when the relationships

between data become too intertwined.

• Difficult to Update: Changing or updating the structure can be challenging due to

the many relationships.

• Querying Issues: Writing queries for complex relationships can be more difficult than

in relational models.

Comparing the Three Models:

Feature

Relational Model

Hierarchical Model

Network Model

Structure

Tables with rows and

columns

Tree-like structure

Graph-like structure with

many-to-many links

Relationships

One-to-one, one-to-

many, many-to-many

One-to-many

Many-to-many

Flexibility

High (easy to

add/remove data)

Low (rigid structure)

Medium (more flexible

than hierarchical)

Efficiency

Moderate (depends on

query)

High (due to tree

traversal)

High (multiple access

paths)

Ease of Use

Easy to understand and

use

Simple but limited

More complex

Data

Redundancy

Low

High

Low

Query

Complexity

Simple (SQL)

Complex (especially for

non-hierarchical queries)

Complex (especially for

complex relationships)

Easy2Siksha

Conclusion:

• Relational model is best suited for modern applications where data integrity,

scalability, and flexibility are important.

• Hierarchical model works well when data has a clear parent-child relationship, but

it's not ideal for complex systems.

• Network model is more flexible than the hierarchical model but can become

complex to manage.

Each model has its pros and cons, and the choice depends on the specific needs of the

application. In modern databases, the relational model is the most common because of its

flexibility and ease of use, while the hierarchical and network models are more specialized.

4. What is the need of normalization? Explain BCNF, INF, 2NF and 4NF normal forms.

Ans: Introduction to Normalization

When we talk about databases, one of the most crucial things to understand is

normalization. Normalization is a process used in database design to organize data

efficiently. It involves dividing large tables into smaller, more manageable ones and ensuring

that data is stored logically, which reduces redundancy and inconsistency. Redundancy

means repeating the same data in multiple places, and inconsistency happens when the

same data is updated in one place but not in others. Both can lead to errors and make the

database harder to manage.

Normalization is essential for various reasons:

• Eliminating redundant data: Repeating the same data unnecessarily takes up more

storage space and can lead to inaccuracies.

• Ensuring data integrity: Ensures that when data is updated in one table, it is

correctly reflected across the database.

• Efficient data querying: Well-organized databases are easier to query, which speeds

up searches and reduces the risk of errors.

• Easy maintenance: When data is well-structured, it’s easier to maintain the database

as it grows.

Now, let’s go through different Normal Forms used in normalization. Each of these forms

has specific rules that help ensure the data is organized in the best way possible.

Easy2Siksha

1NF (First Normal Form)

1NF focuses on ensuring that each table in a database is organized in a straightforward way.

A table is said to be in 1NF if it meets these rules:

• Atomicity: Each column in the table should hold only one value. There should not be

multiple values in a single column.

• Uniqueness: Each record in the table should be unique. There should be a unique

identifier, usually called a primary key, that distinguishes one record from another.

• No repeating groups: There shouldn’t be any columns that store multiple values or

repeated information in the same row.

Example:

Imagine a table storing information about students and their courses:

Student_ID

Name

Courses

John

Math, Science

Alice

Science, English

This table violates 1NF because the "Courses" column holds multiple values. To fix this, you

split the table:

Student_ID

Name

Course

John

Math

John

Science

Alice

Science

Alice

English

Now, the table is in 1NF because each column holds atomic values.

2NF (Second Normal Form)

Once a table is in 1NF, the next step is to ensure it follows the rules of 2NF. The main goal of

2NF is to remove partial dependency. This means that no non-prime attribute (i.e.,

Easy2Siksha

attributes that are not part of the primary key) should depend on only part of the primary

key.

• Fully Functional Dependency: In 2NF, all non-key columns must depend on the entire

primary key, not just part of it.

Example:

Consider this table where each student can have multiple courses, and each course is taught

by a specific teacher:

Student_ID

Course

Teacher

Math

Mr. Smith

Science

Mrs. Adams

Science

Mrs. Adams

English

Mr. Brown

Here, Student_ID and Course together form the primary key. But the "Teacher" column only

depends on the "Course" column, not the "Student_ID". This violates 2NF.

To fix this, we split the table:

1. Student Table:

Student_ID

Course

Math

Science

English

Easy2Siksha

2. Course-Teacher Table:

Course

Teacher

Math

Mr. Smith

Science

Mrs. Adams

English

Mr. Brown

Now, the "Teacher" depends only on the "Course" and not partially on the primary key,

making it 2NF-compliant.

3NF (Third Normal Form)

A table is in 3NF if it is in 2NF and all the attributes are functionally dependent only on the

primary key. The goal of 3NF is to eliminate transitive dependencies. A transitive

dependency occurs when one non-key attribute depends on another non-key attribute.

• No Transitive Dependencies: In 3NF, no non-key column should depend on another

non-key column.

Example:

Consider this table:

Student_ID

Student_Name

Department

Head_of_Department

John

Science

Dr. Clark

Alice

Arts

Dr. Johnson

Here, "Head_of_Department" depends on "Department", not "Student_ID", which violates

3NF. To fix this, you create a separate table for departments:

Easy2Siksha

1. Student Table:

Student_ID

Student_Name

Department

John

Science

Alice

Arts

2. Department Table:

Department

Head_of_Department

Science

Dr. Clark

Arts

Dr. Johnson

Now, the table is in 3NF because "Head_of_Department" only depends on the

"Department" key.

BCNF (Boyce-Codd Normal Form)

BCNF is an extension of 3NF, but it is stricter. A table is in BCNF if it is in 3NF and for every

functional dependency (X → Y), X should be a superkey. A superkey is a set of columns that

can uniquely identify a row in the table.

Example:

Consider this table:

Student_ID

Course

Instructor

Math

Mr. Smith

Science

Mrs. Adams

Science

Mrs. Adams

Easy2Siksha

Here, "Student_ID" and "Course" together are the primary key, but "Instructor" also

depends on the "Course". This violates BCNF because "Course" should be a superkey.

To resolve this, we split the table into two:

1. Student-Course Table:

Student_ID

Course

Math

Science

2. Course-Instructor Table:

Course

Instructor

Math

Mr. Smith

Science

Mrs. Adams

This ensures that the functional dependency is correctly organized, making the table BCNF-

compliant.

4NF (Fourth Normal Form)

A table is in 4NF if it has no multi-valued dependencies. A multi-valued dependency occurs

when one attribute in a table uniquely determines another attribute, but there is still some

other independent attribute.

• No Multi-Valued Dependencies: In 4NF, a table should not have more than one

multi-valued dependency.

Easy2Siksha

Example:

Imagine a table that lists the hobbies and languages spoken by students:

Student_ID

Hobby

Language

Reading

English

Swimming

English

Reading

French

Painting

Spanish

In this case, the student has multiple hobbies and speaks multiple languages, which creates

a multi-valued dependency. To fix this, you split the table:

1. Student-Hobby Table:

Student_ID

Hobby

Reading

Swimming

Painting

2. Student-Language Table:

Student_ID

Language

English

French

Spanish

Easy2Siksha

This ensures there are no multi-valued dependencies, making the tables 4NF-compliant.

Conclusion

Normalization is essential in organizing databases to reduce redundancy, avoid

inconsistency, and ensure efficient querying and maintenance. The progression from 1NF to

4NF (and further) ensures that the database structure is optimal for long-term use. Each

normal form builds upon the previous one, ensuring that the data stored is both accurate

and efficient.

SECTION-C

5. Describe the concept of Referential Integrity. List and explain the common data types

available in SQL.

Ans: Concept of Referential Integrity

Referential Integrity is an important concept in relational database management systems

(RDBMS) that ensures data consistency and accuracy across related tables. In simple terms,

it prevents the creation of incorrect or orphaned relationships between tables. Here’s a

breakdown of how it works and why it matters:

1. Foreign Key Relationships: Referential integrity is primarily maintained through

foreign keys. A foreign key is a column or a set of columns in one table that links to

the primary key in another table. The primary key uniquely identifies each row in a

table, while the foreign key establishes a relationship with this primary key in

another table. For example, in a library database, you might have a "Books" table

with a primary key called book_id. In the "Loans" table, there could be a foreign key

book_id that references the primary key in the "Books" table. This ensures that every

loan record is associated with a valid book.

2. Data Consistency: Referential integrity ensures that foreign key values must always

correspond to valid primary key values in the referenced table. For example, if a

customer places an order, the order record should always reference an existing

customer in the "Customers" table. If you try to delete a customer who still has

orders, the database will prevent this, maintaining consistency. This means you

cannot have "orphan" records — records in one table that refer to non-existent

records in another table.

3. Cascading Actions: When a primary key value is changed or deleted, referential

integrity defines certain rules on what happens to the related foreign key values:

Easy2Siksha

o CASCADE: If the primary key is updated or deleted, the related foreign key

values in other tables are also updated or deleted. This ensures that changes

in one table reflect across related tables.

o SET NULL: If the primary key is deleted, the foreign key values are set to

NULL. This is useful when you want to keep the foreign key records but

indicate that they no longer have a valid relationship with the primary key.

o RESTRICT or NO ACTION: The database will restrict the deletion or update of

a primary key if it has related foreign key values, preventing actions that

could break referential integrity.

o SET DEFAULT: The foreign key is set to a default value if the referenced

primary key is deleted.

These cascading actions help maintain the integrity of the data by ensuring that all

relationships between tables remain valid, even when changes are made to the data.

4. Real-World Examples:

o Retail Store: In a retail database, an "Orders" table might have a foreign key

customer_id that references the id in a "Customers" table. Referential

integrity ensures that every order is linked to a valid customer. If a customer

is deleted, their orders could either be deleted (using CASCADE) or left in the

system with NULL values for the customer_id (using SET NULL).

o Library System: A "Loans" table might reference the book_id in a "Books"

table to ensure that every loan refers to a real book in the library's inventory.

This prevents the system from recording loans for books that do not exist.

o University Database: A "Grades" table could have a foreign key student_id

that references the id in a "Students" table. This guarantees that all grades

are assigned to existing students.

Common Data Types in SQL

SQL supports various data types to define the kind of data that can be stored in a table’s

columns. Here are some of the most commonly used ones:

1. Numeric Data Types:

o INT: Stores whole numbers (integers). Example: 1, 100, -25.

o FLOAT / DOUBLE: Used for storing floating-point numbers, which have

decimals. Example: 3.14, 0.001.

o DECIMAL: Used for precise decimal values, especially in financial applications.

Example: 99.99, 123.45.

Easy2Siksha

2. String Data Types:

o CHAR(n): A fixed-length character string, where n specifies the number of

characters. If the data is shorter than n, it will be padded with spaces.

Example: CHAR(5) will store "hi" as "hi ".

o VARCHAR(n): A variable-length string that can store up to n characters. It

saves space compared to CHAR because it doesn’t pad the string. Example:

VARCHAR(10) can store "hello".

o TEXT: Used for very long strings of text, such as descriptions or comments.

3. Date and Time Data Types:

o DATE: Stores dates in the format YYYY-MM-DD. Example: '2024-09-18'.

o TIME: Stores time in the format HH:MM:SS. Example: '14:35:00'.

o DATETIME: Combines both date and time. Example: '2024-09-18 14:35:00'.

4. Boolean Data Type:

o BOOLEAN: Stores TRUE or FALSE values. In some databases, this is

represented as 1 (TRUE) or 0 (FALSE).

5. Binary Data Types:

o BLOB (Binary Large Object): Used for storing large binary data such as

images, videos, or audio files.

6. Miscellaneous Data Types:

o ENUM: Stores one value from a predefined list of values. Example: An ENUM

field for a column status might allow only 'pending', 'approved', or 'rejected'

values.

o SET: Similar to ENUM but allows storing multiple values from the predefined

list.

Conclusion

Referential integrity ensures that relationships between tables in a relational database

remain accurate and consistent. By enforcing rules on how foreign key values must relate to

primary keys, it prevents orphaned or incorrect records, ensuring data quality across the

database. Additionally, SQL’s diverse set of data types allows database designers to specify

exactly what kind of data each column can store, ensuring efficient storage and data

retrieval. Together, referential integrity and proper use of data types help maintain a well-

structured and reliable database

Easy2Siksha

6. By considering an example describe various data control and update operations in SQL.

Ans: Understanding SQL and Its Importance

SQL, or Structured Query Language, is the standard language used to manage and

manipulate databases. It allows you to create, read, update, and delete data (often referred

to as CRUD operations). Additionally, SQL helps in controlling access to the data, ensuring

that only authorized users can perform certain operations.

Example Database: Bookstore

To illustrate SQL operations, we will use an example of a bookstore database that manages

information about books, authors, prices, and stock levels.

Step 1: Creating the Books Table

First, we need to create a table that will store our book data. Here’s how to do it:

• BookID: A unique identifier for each book (integer).

• Title: The title of the book (up to 100 characters).

• Author: The name of the author (up to 100 characters).

• Price: The price of the book, stored as a decimal (with two decimal points).

• Stock: The number of copies available in stock (integer).

Step 2: Inserting Data into the Table

Now that we have a table, we can add some initial data. This is done using the INSERT

statement.

Easy2Siksha

In this example, we added three books with their respective details.

Step 3: Updating Records

If we need to change information in our database, we use the UPDATE statement. This

might involve changing the price of a book or updating the stock level after a sale.

Example: Updating the Price of a Book

Suppose we want to increase the price of "1984" from $8.99 to $9.99. Here’s how we would

do that:

Example: Updating Stock Levels

If we sell two copies of "The Great Gatsby," we need to decrease its stock:

Step 4: Deleting Records

When a book is no longer available for sale, we can remove it from our database using the

DELETE statement.

Example: Deleting a Book

If we decide to remove "To Kill a Mockingbird," we would execute the following command:

Easy2Siksha

Step 5: Data Control Operations

Data control operations manage who can access and modify the data in the database. This is

crucial for maintaining data security and integrity.

1. Granting Permissions

To allow a user (let’s say librarian) to view and update the book records, we can use the

GRANT statement:

• SELECT: Permission to read data from the table.

• UPDATE: Permission to modify existing records.

2. Revoking Permissions

If we later decide that the librarian should no longer have update permissions, we can

revoke those permissions:

Step 6: Querying Data

Apart from updating and controlling access, you often need to retrieve data from your

database. This is done using the SELECT statement.

Example: Selecting All Books

To view all books in the database, we would run:

This command retrieves all columns for every book in the Books table.

Advanced Queries

You can also use conditions to filter results:

Easy2Siksha

Example: Selecting Books by a Specific Author

If we want to find all books written by George Orwell, we can write:

Aggregate Functions

SQL allows you to perform calculations on data using aggregate functions.

Example: Finding the Average Price of Books

To calculate the average price of all books in our table, we would use:

Conclusion

In this example of a bookstore database, we’ve covered the basic SQL operations involved in

managing and manipulating data. Here’s a recap of what we learned:

1. Creating Tables: Set up a structured format to store data.

2. Inserting Data: Add records to your database.

3. Updating Records: Modify existing data as needed.

4. Deleting Records: Remove data that is no longer relevant.

5. Data Control Operations: Manage user permissions for security.

6. Querying Data: Retrieve and manipulate information stored in the database.

Easy2Siksha

SECTION-D

7. Why the concurrency control is required in data bases? Explain various concurrency

control mechanisms.

Ans: Concurrency control in a database management system (DBMS) is crucial to ensuring

that multiple transactions can occur simultaneously without causing conflicts or

inconsistencies in the data. Let's break this down and understand why concurrency control

is necessary and the various mechanisms that help manage it.

Why Concurrency Control is Required:

In a multi-user database system, multiple transactions are likely to occur at the same time.

Each transaction involves operations like reading, writing, or updating data. Without proper

management, this concurrent access can lead to several issues:

1. Data Inconsistency: When multiple transactions try to access and modify the same

data simultaneously, the results can be unpredictable and incorrect. For example,

two users might attempt to withdraw money from the same bank account, but

without concurrency control, the system might not register one of the withdrawals

properly, leaving the account with an incorrect balance.

2. Dirty Reads: This occurs when a transaction reads data that has been modified by

another transaction but not yet committed. If the modifying transaction fails or is

rolled back, the first transaction will have read invalid data.

3. Lost Updates: If two transactions read the same value, then both update it, one of

the updates might be lost because each transaction didn’t see the changes made by

the other.

4. Phantom Reads: This happens when a transaction reads a set of rows that meet a

condition, but another transaction inserts or deletes rows that affect this result set

before the first transaction completes.

Without concurrency control mechanisms, these problems could compromise the accuracy,

consistency, and reliability of the database, particularly in environments with many users

accessing the system simultaneously.

Concurrency Control Mechanisms:

To address the issues mentioned above, DBMS uses various concurrency control techniques.

These mechanisms ensure that transactions are executed in a manner that maintains the

integrity of the database while allowing for optimal performance.

1. Lock-Based Protocols:

Locks are mechanisms that restrict access to data items while a transaction is working on

them. There are two main types of locks:

Easy2Siksha

• Shared Lock (S-Lock): Allows multiple transactions to read a data item

simultaneously, but not write to it. This ensures that data can be read without

interference, but no changes can be made until the lock is released.

• Exclusive Lock (X-Lock): Prevents both read and write access to a data item by other

transactions. Only the transaction holding the lock can modify or read the data.

Various locking protocols can be used:

• Two-Phase Locking (2PL): In this protocol, a transaction follows two phases:

1. Growing Phase: Locks are acquired but none are released.

2. Shrinking Phase: Locks are released, but no new locks are acquired.

This ensures that once a transaction begins to release locks, it can no longer request any,

thus preventing conflicts. An extension of this is Strict Two-Phase Locking, where locks are

only released after the transaction is completed and committed, ensuring complete

isolation.

• Simplistic Lock Protocol: A simple approach where a transaction locks all data before

any operation begins and releases the locks only after all operations are done.

However, this can reduce performance as transactions hold locks for too long.

• Pre-claiming Lock Protocol: Before starting, this protocol checks if all required locks

are available. If they are, it proceeds; if not, it aborts or rolls back the transaction to

prevent deadlock situations.

2. Timestamp-Based Protocols:

Every transaction is given a timestamp based on when it began. This timestamp determines

the order in which transactions should be executed. The goal is to ensure serializability,

meaning the result of the transactions is the same as if they were executed one after

another in some order.

• Timestamp Ordering Protocol: Operations are executed based on the timestamps of

transactions. If a transaction tries to read or write data that another transaction with

an earlier timestamp has already accessed, it may be rolled back to maintain the

correct order.

• Multiversion Concurrency Control (MVCC): In this protocol, the system maintains

multiple versions of data. When a transaction reads data, it can read a version that

existed at the time the transaction began, while other transactions can continue to

update the data. This method avoids conflicts and improves performance in read-

heavy systems.

3. Validation-Based Protocols:

In this approach, transactions go through three phases:

Easy2Siksha

1. Read Phase: The transaction reads data and makes changes locally without affecting

the database.

2. Validation Phase: Before committing, the system checks if the changes can be

applied without violating any constraints.

3. Write Phase: If validation is successful, the changes are applied to the database.

If validation fails, the transaction is rolled back. This protocol is effective when transactions

tend to perform many reads but relatively few writes.

Problems Solved by Concurrency Control:

1. Lost Update Problem: This occurs when two transactions update the same data

simultaneously, and one update overwrites the other. Concurrency control ensures

that only one transaction can update the data at a time, preventing this issue.

2. Dirty Reads: By enforcing locks or using timestamp protocols, a transaction is

prevented from reading uncommitted data, ensuring that only valid, committed data

is read.

3. Uncommitted Dependency Problem (also known as the cascading rollback): If a

transaction depends on uncommitted changes made by another transaction, and

that transaction is rolled back, the dependent transaction might also need to be

rolled back. Concurrency control mechanisms, such as strict two-phase locking,

prevent transactions from accessing uncommitted data.

4. Inconsistent Data: Concurrency control ensures that all transactions appear as if

they are executed serially, maintaining data consistency.

Importance of Concurrency Control:

1. Data Integrity: It ensures that the database remains accurate and consistent, even

with multiple transactions occurring at the same time.

2. Efficiency: Allows multiple transactions to be processed simultaneously, improving

system performance and resource utilization.

3. System Reliability: Prevents issues like lost updates or inconsistent data, which could

lead to system failures or inaccurate results.

4. User Satisfaction: In multi-user environments, concurrency control ensures that

each user has a smooth experience without interference from other users’

transactions.

Conclusion:

Concurrency control is essential in a DBMS to manage the simultaneous execution of

transactions while preserving data integrity and ensuring consistent results. Various

mechanisms, like locking protocols, timestamp ordering, and validation-based protocols,

help to achieve this by controlling access to data and resolving potential conflicts. Effective

Easy2Siksha

concurrency control makes databases more robust, efficient, and reliable in multi-user

environments

8. Write short notes on the following:-

(a) Big Data Analytics

(b) NoSQL

Ans: sure! Let’s break down each topic related to your Database Management System

(DBMS) and Oracle studies into simple explanations that are easy to understand. I will

explain Big Data Analytics, NoSQL, and Database Security in detail and try to keep the

explanation clear and straightforward.

(a) Big Data Analytics

Big Data Analytics refers to the process of examining large, complex datasets—often called

"big data"—to uncover patterns, correlations, trends, and useful information. This process is

important because data is growing rapidly, and traditional data processing tools can't

handle such massive amounts of data.

Key Points about Big Data:

1. Volume: Big data deals with extremely large datasets, ranging from terabytes to

petabytes of data.

2. Velocity: This refers to the speed at which data is generated and processed. For

example, data from social media, sensors, or website activity can be produced very

fast.

3. Variety: Big data comes from many sources and can be structured (like databases),

unstructured (like emails or social media posts), or semi-structured (like XML files).

4. Veracity: The quality of the data, ensuring it is accurate and reliable.

5. Value: The ultimate goal is to derive value from the data, turning it into useful

information for decision-making.

Why is Big Data Analytics Important?

Big data analytics helps organizations:

• Understand customer behavior: For example, analyzing social media comments can

reveal what customers think about a product.

• Improve decision-making: Data-driven decisions based on patterns and trends are

more likely to succeed.

Easy2Siksha

• Optimize operations: By analyzing data from various business processes, companies

can streamline operations.

• Detect fraud: Big data analytics helps detect unusual patterns, which could indicate

fraud.

Tools Used in Big Data Analytics:

• Hadoop: A popular tool for storing and processing large datasets.

• Apache Spark: A fast engine for large-scale data processing.

• Tableau: A data visualization tool that helps make sense of large datasets by creating

easy-to-read charts and graphs.

Example of Big Data Analytics:

An e-commerce company might collect data from millions of customer interactions on its

website. By analyzing this data, they can understand purchasing habits, predict future

trends, and recommend products to customers.

(b) NoSQL

NoSQL refers to a category of database management systems that are designed to handle

large amounts of data and can easily scale to meet the needs of modern applications. Unlike

traditional relational databases (like SQL), NoSQL databases do not rely on structured data

and tables with fixed columns and rows.

Why is NoSQL Needed?

• Flexibility: In today’s world, data comes in many different forms (text, images,

videos), and NoSQL can handle all of these types easily.

• Scalability: NoSQL databases can grow with the needs of an application. They can

store enormous amounts of data across many servers without slowing down.

• Performance: NoSQL databases are designed for fast read/write operations, which is

critical for high-performance applications like social media or gaming platforms.

Types of NoSQL Databases:

1. Document-Oriented Databases: Stores data in document format (like JSON or XML).

Example: MongoDB.

2. Key-Value Stores: Every item in the database is stored as a key-value pair, like a

dictionary. Example: Redis.

3. Column-Oriented Databases: Stores data in columns instead of rows, which makes it

faster to retrieve. Example: Cassandra.

4. Graph Databases: Stores relationships between data in the form of graphs, making

them ideal for social networks. Example: Neo4j.

Easy2Siksha

Advantages of NoSQL:

• Schema-less: NoSQL databases are flexible and don’t require a fixed table structure.

This makes it easy to modify data structures on the go.

• Horizontal Scaling: As data grows, NoSQL databases can easily add more servers to

handle increased load.

• High Availability: Many NoSQL databases are designed to continue working even if

some of the system components fail, ensuring there’s no downtime.

Example of NoSQL Use:

A social media platform that handles millions of posts, likes, comments, and messages every

second needs a database that can store all this data efficiently and quickly. NoSQL

databases, like MongoDB, can manage this enormous amount of unstructured data in a fast

and scalable way.

Database Security refers to the practices and technologies used to protect a database from

unauthorized access, corruption, or misuse. Since databases often contain sensitive and

valuable information, ensuring their security is critical.

Key Threats to Database Security:

1. Unauthorized Access: Someone could gain access to the database and view, change,

or delete information without permission.

2. SQL Injection: A common hacking technique where attackers inject malicious SQL

commands into a query, allowing them to manipulate the database.

3. Data Corruption: Data can be accidentally or deliberately corrupted, leading to

inaccurate or unusable data.

4. Insider Threats: Employees or insiders who have legitimate access to the database

might misuse their privileges to steal or manipulate data.

Key Concepts in Database Security:

1. Authentication: Ensures that only authorized users can access the database. This

usually involves verifying the identity of users through usernames, passwords, or

multi-factor authentication (MFA).

2. Authorization: Once authenticated, users are granted certain permissions based on

their role. For example, a regular user might only be able to read data, while an

administrator can add, edit, or delete records.

3. Encryption: Data encryption is the process of encoding data so that only authorized

users can read it. This protects sensitive data from being accessed by unauthorized

users.

Easy2Siksha

4. Backups: Regular backups ensure that in case of data loss (due to an attack or

accident), the data can be restored from a previous state.

5. Auditing: Auditing tracks what users are doing in the database, providing logs that

can help detect unusual activity.

Best Practices for Database Security:

• Use Strong Passwords: Weak passwords are easy to guess or crack, so it’s essential

to use strong, complex passwords.

• Limit User Access: Only grant users the permissions they need to perform their job,

and nothing more. This is known as the "least privilege" principle.

• Monitor Database Activity: Continuous monitoring of database activity can help

detect suspicious behavior, such as unauthorized access attempts.

• Keep Software Updated: Database software should be updated regularly to fix

security vulnerabilities.

Database Security Tools:

• Firewalls: A firewall can block unauthorized access to the database by controlling

incoming and outgoing traffic.

• Data Masking: Masking hides sensitive data (like credit card numbers) so that even if

someone accesses the data, it’s not useful.

• Intrusion Detection Systems (IDS): IDS monitors database traffic and looks for

patterns that might indicate an attack.

Example of Database Security:

Consider a hospital's database that contains sensitive patient information. To protect this

data, the hospital would use authentication (like requiring doctors to log in), encryption (to

ensure data is safe even if someone intercepts it), and regular audits (to track who accessed

the data).

Conclusion:

• Big Data Analytics helps businesses and organizations deal with massive amounts of

data by using advanced tools to analyze patterns, predict trends, and make

decisions.

• NoSQL databases provide a flexible, scalable, and high-performance alternative to

traditional relational databases, making them perfect for handling large-scale and

unstructured data.

• Database Security is crucial in safeguarding sensitive data from unauthorized access,

corruption, and misuse by implementing a combination of tools and best practices

like encryption, authentication, and auditing.

Easy2Siksha

These three topics are highly relevant in today’s digital world, where data is constantly

growing, and its protection is more important than ever. By understanding these concepts,

you can see how businesses manage and secure their valuable data.

Note: This Answer Paper is totally Solved by Ai (Artificial Intelligence) So if You find Any Error Or Mistake . Give us a

Feedback related Error , We will Definitely Try To solve this Problem Or Error.